Database Questions and Answers – Functional-Dependency Theory – Algorithms for Decomposition
1.Suppose relation R(A,B,C,D,E) has the following functional dependencies:
A -> B B -> C BC -> A A -> D E -> A D -> E
Which of the following is not a key?
a) A
b) E
c) B, C
d) D
Answer: c Explanation: Here the keys are not formed by B and C.
2.Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold:
AB->CD DE->P C->E P->C B->G
The relation schema R is
a) in BCNF
b) in 3NF, but not in BCNF
c) in 2NF, but not in 3NF
d) not in 2NF
Answer: d
Explanation: From the closure set of attributes we can see that the key for the relation is AB. The FD B->G is a partial dependency, hence it is not in 2NF.
3.A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies:
F1->F3 F2->F4 (F1,F2)->F5
in terms of normalization, this table is in
a) 1NF
b) 2NF
c) 3NF
d) None of the mentioned
Answer: a
Explanation: Since the primary key is not given we have to derive the primary key of the table. Using the closure set of attributes we get the primary key as (F1, F2). From functional dependencies, “F1->F3, F2->F4”, we can see that there is partial functional dependency therefore it is not in 1NF. Hence the table is in 1NF.
4.Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
a) Zero
b) More than zero but less than that of an equivalent 3NF decomposition
c) Proportional to the size of F+
d) Indeterminate
Answer: b
Explanation: Redundancy in BCNF is low when compared to 3NF. For more details on BCNF.
5. The algorithm that takes a set of dependencies and adds one schema at a time, instead of decomposing the initial schema repeatedly is
a) BCNF algorithm
b) 2NF algorithm
c) 3NF synthesis algorithm
d) 1NF algorithm
Explanation: The result is not uniquely defined, since a set of functional dependencies can have more than one canonical cover, and, further, in some cases, the result of the algorithm depends on the order in which it considers the dependencies in Fc.
6. The functional dependency can be tested easily on the materialized view, using the constraints ____________.
a) Primary key
b) Null
c) Unique
d) Both Null and Unique
Explanation: Primary key contains both unique and not null constraints.
7. Which normal form is considered adequate for normal relational database design?
a) 2NF
b) 5NF
c) 4NF
d) 3NF
Explanation: A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.
8. R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency preserving BCNF decomposition?
a) A->B, B->CD
b) A->B, B->C, C->D
c) AB->C, C->AD
d) A->BCD
Explanation: This relation gives a relation without any loss in the values.
This set of Database Multiple Choice Questions & Answers (MCQs) focuses on “Functional-Dependency Theory”.
9. We can use the following three rules to find logically implied functional dependencies. This collection of rules is called
a) Axioms
b) Armstrong’s axioms
c) Armstrong
d) Closure
Explanation: By applying these rules repeatedly, we can find all of F+, given F.
10. Which of the following is not Armstrong’s Axiom?
a) Reflexivity rule
b) Transitivity rule
c) Pseudotransitivity rule
d) Augmentation rule
Explanation: It is possible to use Armstrong’s axioms to prove that Pseudotransitivity rule is sound.